package com.hefan.club.dynamic.dao;

import com.cat.common.entity.Page;
import com.hefan.club.dynamic.bean.TripInfo;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class TripInfoDao {

    @Resource
    JdbcTemplate jdbcTemplate;

    private static org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(TripInfoDao.class);

    /**
     * 获取行程类型列表
     *
     * @return
     * @throws @date: 2016年10月15日 下午4:34:25
     * @Title: findTripsTypeInfoList
     * @Description: TODO(这里用一句话描述这个方法的作用)
     * @return: List
     * @author: LiTeng
     */
    public List findTripsTypeInfoList() {
        String sql = "SELECT id as type,type_name as typeStr from trip_type_info order by sort asc";
        return this.jdbcTemplate.queryForList(sql);
    }

    public int findTripsTypeCountById(String type) {
        String sql = "SELECT count(1) count from trip_type_info where id=?";
        Integer count = this.jdbcTemplate.queryForObject(sql, Integer.class, type);
        return count == null ? 0 : count;
    }

    /**
     * 获取给定时间行程条数
     *
     * @param userId
     * @param time
     * @param timeFormat
     * @param tripId
     * @return
     * @throws
     * @Title: findTripsCountByTime
     * @Description: TODO(这里用一句话描述这个方法的作用)
     * @return: int
     * @author: LiTeng
     * @date: 2016年10月17日 上午11:08:43
     */
    public int
    findTripsCountByTime(String userId, String time, String timeFormat, long tripId) {
        StringBuilder sql = new StringBuilder();
        sql.append("select count(1) num from trip_info ").append(" where user_id=? and id !=?");
        if (StringUtils.isNotBlank(time)) {
            sql.append(" and DATE_FORMAT(time," + timeFormat + ")=DATE_FORMAT('" + time + "'," + timeFormat + ")");
        }
        Integer num = this.jdbcTemplate.queryForObject(sql.toString(), Integer.class, userId, tripId);
        return num == null ? 0 : num;
    }

    /**
     * 保存
     *
     * @param tripInfo
     * @return
     * @throws
     * @Title: saveTripInfo
     * @Description: TODO(这里用一句话描述这个方法的作用)
     * @return: int
     * @author: LiTeng
     * @date: 2016年10月17日 上午11:08:30
     */
    public int saveTripInfo(TripInfo tripInfo) {
        String insertSql = "insert into trip_info(user_id,img_url,title,content,type,time,activity_id,link,is_open,opentime,trip_city,address)"
                + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
        // 参数数组
        Object[] paramArr = new Object[]{tripInfo.getUserId(), tripInfo.getImgUrl(), tripInfo.getTitle(),
                tripInfo.getContent(), tripInfo.getType(), tripInfo.getTime(), tripInfo.getActivityId(),
                tripInfo.getLink(), tripInfo.getIsOpen(), tripInfo.getOpentime(), tripInfo.getTripCity(),
                tripInfo.getAddress()};
        return jdbcTemplate.update(insertSql, paramArr);
    }

    public int updateTripInfo(TripInfo tripInfo) {
        String updateSql = "update trip_info set user_id=?,img_url=?,title=?,content=?,type=?,time=?,activity_id=?,link=?"
                + ",is_open=?,opentime=?,trip_city=?,address=? where id=?";
        // 参数数组
        Object[] paramArr = new Object[]{tripInfo.getUserId(), tripInfo.getImgUrl(), tripInfo.getTitle(),
                tripInfo.getContent(), tripInfo.getType(), tripInfo.getTime(), tripInfo.getActivityId(),
                tripInfo.getLink(), tripInfo.getIsOpen(), tripInfo.getOpentime(), tripInfo.getTripCity(),
                tripInfo.getAddress(), tripInfo.getId()};
        return jdbcTemplate.update(updateSql, paramArr);
    }

    /**
     * 删除
     *
     * @param id
     * @return
     * @throws
     * @Title: deleteTripInfo
     * @Description: TODO(这里用一句话描述这个方法的作用)
     * @return: int
     * @author: LiTeng
     * @date: 2016年10月17日 上午11:08:24
     */
    public int deleteTripInfo(long id) {
        String deleteSql = "delete from trip_info where id =?";
        return jdbcTemplate.update(deleteSql, id);
    }

    /**
     * 获取最近一条记录（未来第一条，如果没有，就去过去）
     *
     * @param userId
     * @param isOpen
     * @param direction 1-取过去
     * @return
     * @throws
     * @Title: findTripsInfoLately
     * @Description: TODO(这里用一句话描述这个方法的作用)
     * @return: Map
     * @author: LiTeng
     * @date: 2016年10月17日 上午10:31:51
     */
    public Map findTripsInfoLately(String userId, String isOpen, int direction) {
        String sql = "select a.id,a.user_id as userId,a.img_url as imgUrl,a.title,a.content,a.type,"
                + " ifnull(b.type_name,'未知') typeStr,"
                + " DATE_FORMAT(a.time,'%Y-%m-%d') tripDate,DATE_FORMAT(a.time,'%H:%i') tripHm,"
                + " DATE_FORMAT(a.time,'%Y-%m-%d %H:%i') time,"
                + " if(a.time<now(),0,1) isEnd,a.activity_id as activityId,a.link,a.is_open as isOpen,"
                + " DATE_FORMAT(a.opentime,'%Y-%m-%d') openDate,DATE_FORMAT(a.opentime,'%H:%i') openHm,"
                + " DATE_FORMAT(a.opentime,'%Y-%m-%d %H:%i') opentime,a.address,"
                + " a.trip_city as tripCity from trip_info a LEFT JOIN trip_type_info b on a.type=b.id"
                + " where a.user_id=?";
        if (StringUtils.isBlank(isOpen) || !isOpen.equals("1")) {
            sql += " and a.is_open=1 and NOW()>=IFNULL(a.opentime,NOW())";
        }
        if (direction == 1) {
            sql += " and a.time<=now() order by time desc";
        } else {
            sql += " and a.time>=now() order by time asc";
        }
        sql += " limit 1";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, userId);
        if (list != null && list.size() > 0) {
            return list.get(0);
        } else {
            return new HashMap();
        }
    }

    /**
     * 星空图首次加载
     *
     * @param userId
     * @param isOpen
     * @param mpageSize
     * @param time
     * @return
     * @throws
     * @Title: findTripsForInit
     * @Description: TODO(这里用一句话描述这个方法的作用)
     * @return: List
     * @author: LiTeng
     * @date: 2016年10月17日 上午11:08:08
     */
    public List findTripsForInit(String userId, String isOpen, int mpageSize, String time) {
        String sql = "select * from (select a.id,a.user_id as userId,a.img_url as imgUrl,"
                + " a.title,ifnull(b.type_name,'未知') typeStr,"
                + " a.content,a.type,DATE_FORMAT(a.time,'%Y-%m-%d') tripDate,DATE_FORMAT(a.time,'%H:%i') tripTime,"
                + " DATE_FORMAT(a.time,'%Y-%m-%d %H:%i') time,if(a.time<now(),0,1) isEnd,"
                + " if(DATE_FORMAT(a.time,'%Y-%m-%d %H:%i')=DATE_FORMAT(?,'%Y-%m-%d %H:%i'),1,0) isLately,"
                + " a.activity_id as activityId,a.link,a.is_open as isOpen,DATE_FORMAT(a.opentime,'%Y-%m-%d %H:%i') opentime,"
                + " a.address,a.trip_city as tripCity from trip_info a LEFT JOIN trip_type_info b on a.type=b.id"
                + " where user_id=?";
        if (StringUtils.isBlank(isOpen) || !isOpen.equals("1")) {
            sql += " and a.is_open=1 and NOW()>=IFNULL(a.opentime,NOW())";
        }
        sql += " ORDER BY ABS(TIMESTAMPDIFF(SECOND,?, a.time)*1000) asc"
                + " limit ? ) t order by t.time";
        return this.jdbcTemplate.queryForList(sql, time, userId, time, mpageSize);
    }

    /**
     * 赶时间方向取指定条数的行程
     *
     * @param userId
     * @param isOpen
     * @param tripTime
     * @param mpageSize
     * @param direction
     * @return
     * @throws
     * @Title: findTripsByTimeDirection
     * @Description: TODO(这里用一句话描述这个方法的作用)
     * @return: List
     * @author: LiTeng
     * @date: 2016年10月17日 上午11:07:47
     */
    public List findTripsByTimeDirection(String userId, String isOpen, String tripTime, int mpageSize, String direction) {
        String sql = "select a.id,a.user_id as userId,a.img_url as imgUrl,a.title,"
                + " ifnull(b.type_name,'未知') typeStr,"
                + " a.content,a.type,DATE_FORMAT(a.time,'%Y-%m-%d') tripDate,DATE_FORMAT(a.time,'%H:%i') tripTime,"
                + " DATE_FORMAT(a.time,'%Y-%m-%d %H:%i') time,if(a.time<now(),0,1) isEnd,"
                + " a.activity_id as activityId,a.link,a.is_open as isOpen,DATE_FORMAT(a.opentime,'%Y-%m-%d %H:%i') opentime,"
                + " a.address, a.trip_city as tripCity from trip_info a LEFT JOIN trip_type_info b on a.type=b.id"
                + " where a.user_id=?";
        if (StringUtils.isBlank(isOpen) || !isOpen.equals("1")) {
            sql += " and a.is_open=1 and NOW()>=IFNULL(a.opentime,NOW())";
        }
        if (StringUtils.isNotBlank(direction) && "1".equals(direction)) {
            sql += " and a.time > '" + tripTime + "'";
        } else {
            sql += " and a.time < '" + tripTime + "'";
        }
        sql += " order by a.time asc limit ?";
        return this.jdbcTemplate.queryForList(sql, userId, mpageSize);
    }

    /**
     * 分页获取行程列表（按照时间倒序）
     *
     * @param userId
     * @param isOpen
     * @param page
     * @return
     * @throws
     * @Title: findTripsPage
     * @Description: TODO(这里用一句话描述这个方法的作用)
     * @return: Page
     * @author: LiTeng
     * @date: 2016年10月17日 上午11:07:29
     */
    public Page findTripsPage(String userId, String isOpen, Page page) {
        StringBuffer whereSql = new StringBuffer("");
        if (StringUtils.isBlank(isOpen) || !isOpen.equals("1")) {
            whereSql.append(" and a.is_open=1 and NOW()>=IFNULL(a.opentime,NOW())");
        }
        StringBuffer fromSql = new StringBuffer("");
        fromSql.append("  from trip_info a LEFT JOIN trip_type_info b on a.type=b.id where a.user_id=?").append(whereSql.toString());

        StringBuffer querySql = new StringBuffer("");
        querySql.append("select a.id,a.user_id as userId,a.img_url as imgUrl,a.title,a.content,a.type,")
                .append(" ifnull(b.type_name,'未知') typeStr,")
                .append(" DATE_FORMAT(a.time,'%Y-%m-%d') tripDate,DATE_FORMAT(a.time,'%H:%i') tripHm,")
                .append(" DATE_FORMAT(a.time,'%Y-%m-%d %H:%i') time,if(a.time<now(),0,1) isEnd,")
                .append(" a.activity_id as activityId,a.link,a.is_open as isOpen,")
                .append(" DATE_FORMAT(a.opentime,'%Y-%m-%d') openDate,DATE_FORMAT(a.opentime,'%H:%i') openHm,")
                .append(" DATE_FORMAT(a.opentime,'%Y-%m-%d %H:%i') opentime,a.address,a.trip_city as tripCity")
                .append(fromSql.toString())
                .append(" order by a.time desc limit ").append(page.getOffset()).append(",").append(page.getPageSize());
        List list = jdbcTemplate.queryForList(querySql.toString(), userId);
        if (list != null && list.size() > 0) {
            String countSql = "select COUNT(1) count " + fromSql.toString();
            Long count = jdbcTemplate.queryForObject(countSql, new Object[]{userId}, Long.class);
            page.setTotalItems(count);
        }
        page.setResult(list);
        return page;
    }

    /**
     * 获取个人行程数据（星空图第一版用，之后作废）
     *
     * @param userId
     * @param isOpen
     * @param time
     * @return
     * @throws
     * @Title: findTripsStarChart
     * @Description: TODO(这里用一句话描述这个方法的作用)
     * @return: List
     * @author: LiTeng
     * @date: 2016年10月26日 下午2:35:37
     */
    public List findTripsStarChart(String userId, String isOpen, String time) {
        StringBuffer querySql = new StringBuffer("");
        querySql.append("select a.id,a.user_id as userId,a.img_url as imgUrl,a.title,a.content,a.type,")
                .append(" ifnull(b.type_name,'未知') typeStr,")
                .append(" DATE_FORMAT(a.time,'%Y-%m-%d') tripDate,DATE_FORMAT(a.time,'%H:%i') tripHm,")
                .append(" DATE_FORMAT(a.time,'%Y-%m-%d %H:%i') time,if(a.time<now(),0,1) isEnd,")
                .append(" a.activity_id as activityId,a.link,a.is_open as isOpen,")
                .append(" if(DATE_FORMAT(a.time,'%Y-%m-%d %H:%i')=DATE_FORMAT(?,'%Y-%m-%d %H:%i'),1,0) isLately,")
                .append(" DATE_FORMAT(a.opentime,'%Y-%m-%d') openDate,DATE_FORMAT(a.opentime,'%H:%i') openHm,")
                .append(" DATE_FORMAT(a.opentime,'%Y-%m-%d %H:%i') opentime,a.address,a.trip_city as tripCity")
                .append(" from trip_info a LEFT JOIN trip_type_info b on a.type=b.id where a.user_id=?");
        if (StringUtils.isBlank(isOpen) || !isOpen.equals("1")) {
            querySql.append(" and a.is_open=1 and NOW()>=IFNULL(a.opentime,NOW())");
        }
        querySql.append(" order by a.time desc");
        logger.info("findTripsStarChart sql={}",querySql.toString());
        return this.jdbcTemplate.queryForList(querySql.toString(), time, userId);
    }
}
